import datetime
import re
import tkinter,time,pymysql,threading,gc
import matplotlib.pyplot as plt

class Mysql:
    def mysqlcon(self,host,username,password,port,sql):
        try:
            data=None
            connect=pymysql.connect(host=host,user=username,password=password,port=port)
            dbcursor=connect.cursor()
            #for i in range(10,99):
                #sql=(f'insert into test.info(datetime,ip,cpu,hostname,freetotal,free,SSDIOread,SSDIOwrite,netsed,netre,message) values ("202504131727{i}","192.168.1.2{i}","1.{i}%","web{i}","16G","8G","1{i}kb","{i}kb","2{i}kb","{i}kb","tset{i}");')
            sql=sql
            dbcursor.execute(sql)
            data=dbcursor.fetchall()
        finally:
            connect.commit()
            dbcursor.close()
            connect.close()
            return data

class Gui:
    def __init__(self,host,username,password,port):
        self.host=host
        self.username=username
        self.password=password
        self.port=port
        self.buttonlist=[]
        self.n9=0
        self.root=tkinter.Tk()
        self.shijian=int(datetime.datetime.now().strftime("%Y%m%d%H%M"))-1
        self.sql = f'select hostname,ip,cpu,freetotal,free,SSDIOread,SSDIOwrite,netsed,netre,message from test.info where datetiem="{self.shijian}" order by ip desc limit 20;'
        self.top=["hostname","ip","cpu","freetotal","free","SSDIOread","SSDIOwrite","netsed","netre","message"]
        self.lock=threading.Lock()
        self.root.geometry(f"1280x680+0+0")
        self.root.resizable(False,False)
        # width=self.root.winfo_screenwidth()/2
        # hight=self.root.winfo_screenheight()/2
        # width=str(width).split(".")[0]
        # hight=str(hight).split(".")[0]
        # self.root.configure(background="black")
        # self.root.attributes("-transparentcolor","black")
        # self.root.overrideredirect(True)

        self.button1 = tkinter.Button(width=13,background="black",fg="white",font=("黑体",12,"bold"),text="hostname",height=2,command=self.deschostname)
        self.button1.grid(row=0,column=0)
        self.button2 = tkinter.Button(width=13, background="black", fg="white", font=("黑体", 12, "bold"),text="ip",height=2,command=self.descip)
        self.button2.grid(row=0, column=1)
        self.button3 = tkinter.Button(width=13, background="black", fg="white", font=("黑体", 12, "bold"),text="cpu",height=2,command=self.desccpu)
        self.button3.grid(row=0, column=2)
        self.button4 = tkinter.Button(width=13, background="black", fg="white", font=("黑体", 12, "bold"),text="freetotal",height=2,command=self.descfreetotal)
        self.button4.grid(row=0, column=3)
        self.button5 = tkinter.Button(width=13, background="black", fg="white", font=("黑体", 12, "bold"),text="free",height=2,command=self.descfree)
        self.button5.grid(row=0, column=4)
        self.button6 = tkinter.Button(width=13, background="black", fg="white", font=("黑体", 12, "bold"),text="SSDIOread",height=2,command=self.descSSDIOread)
        self.button6.grid(row=0, column=5)
        self.button7 = tkinter.Button(width=13, background="black", fg="white", font=("黑体", 12, "bold"),text="SSDIOwrite",height=2,command=self.descSSDIOwrite)
        self.button7.grid(row=0, column=6)
        self.button8 = tkinter.Button(width=13, background="black", fg="white", font=("黑体", 12, "bold"),text="netsed",height=2,command=self.descnetsed)
        self.button8.grid(row=0, column=7)
        self.button9 = tkinter.Button(width=13, background="black", fg="white", font=("黑体", 12, "bold"),text="netre",height=2,command=self.descnetre)
        self.button9.grid(row=0, column=8)
        self.button10 = tkinter.Button(width=13, background="black", fg="white", font=("黑体", 12, "bold"),text="message",height=2,command=self.descmessage)
        self.button10.grid(row=0, column=9)

        t1=threading.Thread(target=self.selectdata,args=(host, username, password, port))
        t1.start()
        t2=threading.Thread(target=self.freeshifan)
        t2.start()
        self.root.mainloop()

    def scbt(self,data):
        for i in self.buttonlist:
            i.destroy()
        self.buttonlist=[]
        for i in range(0,len(data)):
            for ii in range(0,len(data[i])):
                self.button1 = tkinter.Button(width=13, background="white", fg="black", font=("黑体", 12, "bold"),text=data[i][ii],command=lambda a=data[i][0],b=data[i][1],c=self.top[ii]: self.butt(a,b,c))
                self.button1.grid(row=i+1, column=ii)
                self.buttonlist.append(self.button1)
    def deschostname(self):
        with self.lock:
            self.sql = f'select hostname,ip,cpu,freetotal,free,SSDIOread,SSDIOwrite,netsed,netre,message from test.info where datetiem="{self.shijian}" order by hostname desc limit 20;'
    def descip(self):
        with self.lock:
            self.sql = f'select hostname,ip,cpu,freetotal,free,SSDIOread,SSDIOwrite,netsed,netre,message from test.info where datetiem="{self.shijian}"  order by ip desc limit 20;'
    def desccpu(self):
        with self.lock:
            self.sql = f'select hostname,ip,cpu,freetotal,free,SSDIOread,SSDIOwrite,netsed,netre,message from test.info where datetiem="{self.shijian}"  order by cpu desc limit 20;'
    def descfreetotal(self):
        with self.lock:
            self.sql = f'select hostname,ip,cpu,freetotal,free,SSDIOread,SSDIOwrite,netsed,netre,message from test.info where datetiem="{self.shijian}"  order by freetoltal desc limit 20;'
    def descfree(self):
        with self.lock:
            self.sql = f'select hostname,ip,cpu,freetotal,free,SSDIOread,SSDIOwrite,netsed,netre,message from test.info where datetiem="{self.shijian}"  order by free desc limit 20;'
    def descSSDIOread(self):
        with self.lock:
            self.sql = f'select hostname,ip,cpu,freetotal,free,SSDIOread,SSDIOwrite,netsed,netre,message from test.info where datetiem="{self.shijian}"  order by SSDIOread desc limit 20;'
    def descSSDIOwrite(self):
        with self.lock:
            self.sql = f'select hostname,ip,cpu,freetotal,free,SSDIOread,SSDIOwrite,netsed,netre,message from test.info where datetiem="{self.shijian}"  order by SSDIOwrite desc limit 20;'
    def descnetsed(self):
        with self.lock:
            self.sql = f'select hostname,ip,cpu,freetotal,free,SSDIOread,SSDIOwrite,netsed,netre,message from test.info where datetiem="{self.shijian}"  order by netsed desc limit 20;'
    def descnetre(self):
        with self.lock:
            self.sql = f'select hostname,ip,cpu,freetotal,free,SSDIOread,SSDIOwrite,netsed,netre,message from test.info where datetiem="{self.shijian}"  order by netre desc limit 20;'
    def descmessage(self):
        if self.n9==0:
            with self.lock:
                self.sql = f'select hostname,ip,cpu,freetotal,free,SSDIOread,SSDIOwrite,netsed,netre,message from test.info where datetiem="{self.shijian}"  order by message desc limit 20;'
                self.n9+=1
        elif self.n9==1:
            with self.lock:
                self.sql = f'select hostname,ip,cpu,freetotal,free,SSDIOread,SSDIOwrite,netsed,netre,message from test.info where datetiem="{self.shijian}"  order by message  limit 20;'
                self.n9=0

    def butt(self,a,b,c):
        t=threading.Thread(target=self.tupiao,args=(a,b,c,))
        t.start()
    def tupiao(self,a,b,c):
        sql=f'select datetime,{c} from test.info where hostname="{a}" and ip="{b}";'
        data = Mysql().mysqlcon(self.host, self.username, self.password, self.port, sql)
        x=[]
        y=[]
        for i in data:
            if "M" in i[1]:
                yy=re.sub("M","",i[1])
                yy=float(yy)/1024
                y.append(yy)
                x.append(i[0])
            elif "G" in i[1]:
                yy = re.sub("G", "", i[1])
                yy = float(yy)
                y.append(yy)
                x.append(i[0])
            else:
                yy=float(i[1])
                x.append(i[0])
                y.append(yy)
        print(x,y)
        plt.plot(x, y)
        plt.xlabel("time")
        plt.xticks(rotation=270)
        plt.ylabel(f"{c}")
        plt.title(a)
        plt.show()

    def selectdata(self,host, username, password, port):
        self.shijian=int(datetime.datetime.now().strftime("%Y%m%d%H%M"))-1
        while True:
            with self.lock:
                self.data = Mysql().mysqlcon(host, username, password, port,self.sql)
            self.scbt(self.data)
            time.sleep(20)

    def datechuli(self):
        self.txt1.delete("1.0",tkinter.END)
        for i in self.data:
            for ii in  range(0,len(i)):
                self.txt1.insert(tkinter.END,f"{i[ii]}\t\t")
            self.txt1.insert(tkinter.END,"\n")
    def freeshifan(self):
        time.sleep(300)
        gc.collect()
if __name__=='__main__':
    Gui("192.168.1.15",'root','Wx-202109',3306)


